<%--
  Created by IntelliJ IDEA.
  User: yoka24443
  Date: 2018-4-8
  Time: 17:49
  To change this template use File | Settings | File Templates.
--%>
<%@ page import="java.util.*,java.sql.*" contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>StudentList</title>
</head>
<body>
    <form action="StudentList.jsp" method="get">
        <h3>学生信息列表</h3>
        <a href="addStudent.jsp">添加学生</a>
        <table border="1" style="border-color: blue;">
            <thead>
                <tr>
                    <th>编号</th>
                    <th>学号</th>
                    <th>姓名</th>
                    <th>班级名称</th>
                    <th>年龄</th>
                    <%--<th>电话</th>--%>
                    <%--<th>地址</th>--%>
                    <%--<th>微信</th>--%>
                    <%--<th>QQ</th>--%>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <%
                    Connection conn = null;
                    Statement statement = null;
                    ResultSet resultSet = null;

                    String studentNO = request.getParameter("studentNO");
                    String realName = request.getParameter("realName");

                    try {
                        //1.加载驱动
                        Class.forName("com.mysql.jdbc.Driver");
                        //2.驱动管理器获取数据库链接
                        conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school?user=root&password=&useUnicode=true&characterEncoding=utf8");
                        //3.获取statement，执行sql
                        statement = conn.createStatement();

                        String sql = "select * from student where 1=1";
                        // 查询条件学号
                        if(studentNO != null && studentNO.trim().length() > 0) {
                            sql += " and studentNO like '%" + studentNO + "%'";
                        } else {
                            studentNO = "";
                        }

                        // 查询条件姓名
                        if(realName != null && realName.trim().length() > 0) {
                            sql += " and realName like '%" + realName + "%'";
                        } else {
                            realName = "";
                        }

                        //4.执行sql返回结果集并解析
                        resultSet = statement.executeQuery(sql);

                        StringBuilder sb = new StringBuilder();
                        int i=0;
                        while(resultSet.next()) {
                            String sNO = resultSet.getString("studentNO");
                            sb.append("<tr>");
                            sb.append("<td>").append(++i).append("</td>");
                            sb.append("<td>").append(sNO).append("</td>");
                            sb.append("<td>").append(resultSet.getString("realName")).append("</td>");
                            sb.append("<td>").append(resultSet.getString("className")).append("</td>");
                            sb.append("<td align='right'>").append(resultSet.getInt("age")).append("</td>");
    //                        sb.append("<td>").append(resultSet.getString("tel")).append("</td>");
    //                        sb.append("<td>").append(resultSet.getString("address")).append("</td>");
    //                        sb.append("<td>").append(resultSet.getString("wechat")).append("</td>");
    //                        sb.append("<td>").append(resultSet.getString("qq")).append("</td>");
                            sb.append("<td>");
                            sb.append(String.format("<a href='viewStudent.jsp?studentNO=%s'>修改</a>", sNO));
                            sb.append("&nbsp;&nbsp;");
                            sb.append(String.format("<a href='delStudent.jsp?studentNO=%s'>删除</a>", sNO));
                            sb.append("</td>");
                            sb.append("</tr>");
                        }
                        sb.append("<tr><td colspan='6' align='right'>").append(String.format("查到&nbsp;<font color='red'>%d</font>&nbsp;记录", i)).append("</td></tr>");
                        out.println(sb.toString());
                    } catch (ClassNotFoundException e) {
                        out.println("连接数据库失败！");
                        e.printStackTrace();
                    } catch (SQLException e) {
                        out.println("SQL执行出错！");
                        e.printStackTrace();
                    } catch (Exception e) {
                        e.printStackTrace();
                    } finally {
                        try {
                            if(resultSet != null) {
                                resultSet.close();
                            }
                            if (statement != null) {
                                statement.close();
                            }
                            if(conn != null) {
                                conn.close();
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                %>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="5">
                        学号：<input id="txt_studentNO" name="studentNO" type="text" value="<%=studentNO%>"/><br/>
                        姓名：<input id="txt_realName" name="realName" type="text" value="<%=realName%>"/>
                    </td>
                    <td>
                        <input type="submit" value="查询"/>
                    </td>
                </tr>
            </tfoot>
        </table>
    </form>
</body>
</html>
